﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

using AKMII.DMRA.Common;
using System.Data;

namespace AKMII.DMRA.DataAccess
{
    public class PowerAccesor
    {
        public void DeletePowerNameTable()
        {
            try
            {
                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, CommandType.Text, "truncate table dbo.PowerName");
            }
            catch (Exception ex)
            {
                throw new Exception("Exception occurs in delete powerName", ex);
            }
        }

        public void InsertPowerName(List<PowerName> powerNames)
        {
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(Constants.ConnectionString))
            {
                try
                {
                    bulkCopy.DestinationTableName = "dbo.PowerName";

                    if (powerNames != null && powerNames.Count > 0)
                    {
                        DataTable table = ConstructDataTable();

                        foreach (PowerName name in powerNames)
                        {
                            object[] parameters = new object[] 
                            {
                                name.FirstName,
                                name.LastName,
                                name.FirmName,
                                name.BusResCity,
                                name.Occupation,
                                name.Tenure,
                                name.BusResZIP,
                                name.Division,
                                name.Market,
                                name.Terr
                            };

                            table.Rows.Add(parameters);
                        }

                        bulkCopy.WriteToServer(table);
                    }
                }
                catch (Exception ex)
                {
                    throw new ApplicationException("Exception occurs in add powernames", ex);
                }
            }
        }

        public List<PowerName> GetPowerNameList(string assignmentName, string city, int number)
        {
            List<PowerName> powerNames = new List<PowerName>();
            const string SpName = "sp_Get_PowerName";
            SqlDataReader reader = null;

            try
            {
                object[] parameters = new object[]
                {
                   assignmentName,
                   city,
                   number
                };

                reader = SqlHelper.ExecuteReader(Constants.ConnectionString, SpName, parameters);

                while (reader.Read())
                {
                    PowerName pn = new PowerName();
                    pn.FirstName = !reader.IsDBNull(0) ? reader.GetString(0) : string.Empty;
                    pn.LastName = !reader.IsDBNull(1) ? reader.GetString(1) : string.Empty;
                    pn.FirmName = !reader.IsDBNull(2) ? reader.GetString(2) : string.Empty;
                    pn.BusResCity = !reader.IsDBNull(3) ? reader.GetString(3) : string.Empty;
                    pn.Occupation = !reader.IsDBNull(4) ? reader.GetString(4) : string.Empty;
                    pn.Tenure = !reader.IsDBNull(5) ? reader.GetInt32(5) : 0;
                    pn.BusResZIP = !reader.IsDBNull(6) ? reader.GetString(6) : string.Empty;
                    pn.Division = !reader.IsDBNull(7) ? reader.GetString(7) : string.Empty;
                    pn.Market = !reader.IsDBNull(8) ? reader.GetString(8) : string.Empty;
                    pn.Terr = !reader.IsDBNull(9) ? reader.GetString(9) : string.Empty;

                    powerNames.Add(pn);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Exception occurs in get powerNames", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return powerNames;
        }

        private DataTable ConstructDataTable()
        {
            DataTable table = new DataTable();

            for (int i = 0; i < 10; i++)
            {
                table.Columns.Add();
            }

            return table;
        }
    }
}
